/*
 * @(#)ExportCommodity.java
 *
 * Copyright 2011 Xinhua Online, Inc. All rights reserved.
 */

package com.winxuan.channel.export.kfuz;


import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;
import org.supercsv.io.CsvMapWriter;
import org.supercsv.io.ICsvMapWriter;
import org.supercsv.prefs.CsvPreference;

import com.winxuan.channel.export.service.ExportService;
import com.winxuan.util.StringUtils;

/**
 * 孔夫子图书数据导出
 * @author  HideHai
 * @version 1.0,2011-5-11
 */
@Service("exportKfuzCommodity")
public class ExportCommodity extends AbstractExportEnv implements ExportService{

	private Logger log = LoggerFactory.getLogger(getClass());
	@Resource
	private JdbcTemplate jdbcTemplate;
	@Resource
	private JdbcTemplate jdbcTemplateOracle;

	/**
	 * 从文本读书商品编号
	 */
	private String inputGoodsId;
	/**
	 * CSV文件输出路径
	 */
	private String outputPath;
	/**
	 * 图片信息文件输出路径
	 */
	private String imageFilePath;

	public void workFlow(){
		fetchCategory();
		writeCommodityToCSV();
	}

	private void readText(ICsvMapWriter csvWriter){
		try {
			Assert.hasText(inputGoodsId, "输入商品编号文件路径不能为空!");
			Assert.hasText(imageFilePath, "输出 图片信息文件路径不能为空!");
			BufferedReader bufferedReader = new BufferedReader(new FileReader(inputGoodsId));
			BufferedWriter bufferedWriter = new BufferedWriter(new FileWriter(imageFilePath));
			String s = null;
			String goodId = null;
			String path = null;
			int i=1;
			while((s = bufferedReader.readLine()) != null){
				log.info(i + " - " + s);
				Map<String, String> map = getCommodityInfoById(s);
				Map<String, ? super Object> data=mergeData(map);
				csvWriter.write(data, header);
				goodId = (String) data.get(header[19]);
				path =(String) map.get("图片");
				if(!StringUtils.isNullOrEmpty(path)){
					path =goodId+","+map.get("图片");
					bufferedWriter.write(path);
					bufferedWriter.newLine();
				}
				i++;
			}
			bufferedReader.close();
			bufferedWriter.close();
			csvWriter.close();
//			System.gc();
		} catch (FileNotFoundException e) {
			log.error("",e);
		} catch (IOException e) {
			log.error("",e);
		}
	}

	private Map<String, ? super Object> mergeData(Map<String, String> map){
		Map<String, ? super Object> data = new HashMap<String, Object>();
		data.put(header[0],map.get("分类") != "" ? getCategory(map.get("分类"), 0) : "");
		data.put(header[1],map.get("分类") != "" ? getCategory(map.get("分类"), 1) : "");
		data.put(header[2],map.get("书名"));
		data.put(header[3],map.get("作者"));
		data.put(header[4],map.get("出版社"));
		data.put(header[5],map.get("图书品相"));
		data.put(header[6],map.get("库存数量"));
		data.put(header[7],map.get("销售价格"));
		data.put(header[8],map.get("出版时间"));
		data.put(header[9],map.get("印刷时间"));
		data.put(header[10],map.get("开本")+"开");
		data.put(header[11],map.get("印张"));
		data.put(header[12],map.get("字数"));
		data.put(header[13],map.get("印数"));
		data.put(header[14],map.get("页数"));
		data.put(header[15],map.get("装订"));
		data.put(header[16],map.get("版次"));
		data.put(header[17],map.get("ISBN"));
		data.put(header[18],map.get("原书价格"));
		data.put(header[19],map.get("货号"));
		data.put(header[20],map.get("描述"));
		data.put(header[21],map.get("图片名"));
		return data;
	}

	private void writeCommodityToCSV(){
		Assert.hasText(outputPath, "输出文件路径不能为空!");
		try {
			ICsvMapWriter csvWriter = new CsvMapWriter(new FileWriter(outputPath), 
					CsvPreference.EXCEL_PREFERENCE);
			csvWriter.writeHeader(header);
			readText(csvWriter);
		} catch (IOException e) {
			log.error("",e);
		}
	}

	private String getCategory(String category,int flag){
		if(flag ==0 ){
			if(categoryMapping.get(category) != null){
				return categoryMapping.get(category)[0];
			}
		}else if(flag ==1){
			if(categoryMapping.get(category) != null){
				return categoryMapping.get(category)[1];
			}
		}
		return "";
	}

	public void test(){
		String sql ="select count(*) from hidehai_category_kfuz";
		log.info("B"+jdbcTemplate.queryForInt(sql));
	}

	private Map<String, String> getCommodityInfoById(String goodsId){
		final Map<String, String> commodityInfoMap = new HashMap<String, String>();
		jdbcTemplateOracle.query(SELECT_COMMODITY_INFO,new Object[]{goodsId},new ResultSetExtractor<Map<String, String>>() {
			@Override
			public Map<String, String> extractData(ResultSet rs) throws SQLException,
			DataAccessException {
				if(rs.next()){
					commodityInfoMap.put("分类",null);
					commodityInfoMap.put("我的分类",null);
					commodityInfoMap.put("书名",rs.getString("书名"));
					commodityInfoMap.put("作者",rs.getString("作者"));
					commodityInfoMap.put("出版社",rs.getString("出版社"));
					commodityInfoMap.put("图书品相","100");
					commodityInfoMap.put("库存数量",rs.getString("库存"));
					commodityInfoMap.put("销售价格",rs.getString("销售价格"));
					commodityInfoMap.put("出版时间",rs.getDate("出版时间") != null ? simpleDateFormat.format(rs.getDate("出版时间")) : null);
					commodityInfoMap.put("印刷时间",rs.getDate("印刷时间") != null ? simpleDateFormat.format(rs.getDate("印刷时间")) : null);
					commodityInfoMap.put("开本",rs.getString("开本"));
					commodityInfoMap.put("印张",rs.getString("印次"));
					commodityInfoMap.put("字数",rs.getString("字数"));
					commodityInfoMap.put("印数",null);
					commodityInfoMap.put("页数",rs.getString("页数"));
					commodityInfoMap.put("装订",null);
					commodityInfoMap.put("版次",rs.getString("印次"));
					commodityInfoMap.put("ISBN",rs.getString("ISBN号"));
					commodityInfoMap.put("原书价格",rs.getString("原书价格"));
					commodityInfoMap.put("货号",rs.getString("编号"));
					commodityInfoMap.put("描述",rs.getString("简介"));
					commodityInfoMap.put("图片路径",rs.getString("图片路径"));
					commodityInfoMap.put("图片名",rs.getString("图片名"));
					commodityInfoMap.put("图片格式",rs.getString("图片格式"));
					commodityInfoMap.put("图片",null);
					commodityInfoMap.put("分类",rs.getString("分类"));
				}
				return commodityInfoMap;
			}
		});
		for(String k : commodityInfoMap.keySet()){
			String v = commodityInfoMap.get(k);
			String filename = null;
			if(v == null){
				commodityInfoMap.put(k,"");
			}
			if(k.equals("描述") && !StringUtils.isNullOrEmpty(v)){
				Matcher matcher	= pattern.matcher(v);
				v = matcher.replaceAll("");
				commodityInfoMap.put("描述", v);
			}
			if(k.equals("字数") && !StringUtils.isNullOrEmpty(v)){
				v = v.replace(".00", "");
				commodityInfoMap.put("字数", v);
			}
			if(k.equals("作者") && StringUtils.isNullOrEmpty(v)){
				commodityInfoMap.put("作者", "不详");
			}
			if(k.equals("图片路径") && !StringUtils.isNullOrEmpty(v)){
				filename = "mid_"+commodityInfoMap.get("图片名")+"."+commodityInfoMap.get("图片格式");
				v += filename;
				commodityInfoMap.put("图片",v);
				commodityInfoMap.put("图片名","images/"+filename);
			}
		}
		return commodityInfoMap;

	}

	@Override
	public short getDistribute() {
		return Short.valueOf("135");
	}

	@Override
	public void fetchCategory() {
		super.fetchCategory();
	}

	private static final String SELECT_COMMODITY_INFO = "SELECT " +
	"	 TBG.GOODS_NO 编号,TBG.BOOK_NAME 书名,TG.MC_CTGY5  分类" +
	",TA.AUTHOR_NAME 作者,TBG.ISBN_ISRC ISBN号,TP.PUBLISHER_NAME 出版社,TGS.SHOW_QUANTITY 库存" +
	",TSG.SELL_PRICE 销售价格,TSG.MARKET_PRICE 原书价格,TBG.EDITION_DATE 出版时间,TBG.CURR_PRINT_ATE 印刷时间" +
	",TBG.SIZE_FORMAT 开本,TBG.PRINT_NUM 印次,TBG.WORD_NUM 字数,TBG.PAGE_NUM 页数,TGI.\"PATH\" 图片路径,TGI.IMG_ID 图片名,TGI.IMG_SUFFIX 图片格式" +
	",TBG.CONTENT_INTRO 简介" +
	" FROM  " +
	"TBL_BOOK_GOODS TBG " +
	"LEFT JOIN TBL_BOOK_PUBLISHER TBP ON TBG.GOODS_NO = TBP.GOODS_NO " +
	"LEFT JOIN TBL_PUBLISHER TP  ON TBP.PUBLISHER_ID = TP.PUBLISHER_ID " +
	"LEFT JOIN TBL_BOOK_AUTHOR TBA  ON TBG.GOODS_NO = TBA.GOODS_NO " +
	"LEFT JOIN TBL_AUTHOR TA  ON  TA.AUTHOR_ID = TBA.AUTHOR_ID " +
	"LEFT JOIN TBL_GOODS_STOCK TGS  ON TGS.GOODS_ID = TBG.GOODS_NO " +
	"LEFT JOIN TBL_USED_IMG TUI  ON TUI.GOODS_ID = TBG.GOODS_NO " +
	"LEFT JOIN TBL_GOODS_IMAGE TGI  ON TGI.img_id = TUI.img_id AND TGI.is_default='Y' AND TGI.mid_flg='Y' " +
	",TBL_SHOP_GOODS TSG" +
	",TBL_GOODS TG " +
	"WHERE " +
	"TSG.GOODS_NO = TBG.GOODS_NO AND " +
	"TBG.GOODS_NO = TG.GOODS_NO AND " +
	"TBG.GOODS_NO =? ";

	private final String[] header = new String[] { "分类", "我的分类", "书名", "作者", "出版社",
			"图书品相", "库存数量", "销售价格", "出版时间", "印刷时间", "开本", "印张", "字数(千字)",
			"印数（千册）", "页数", "装订", "版次", "ISBN书号", "原书价格", "货号", "描述", "图片" };

	private Pattern pattern = Pattern.compile("\\s*|\t|\r|\n");
	private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");

	public void setInputGoodsId(String inputGoodsId) {
		this.inputGoodsId = inputGoodsId;
	}
	public void setOutputPath(String outputPath) {
		this.outputPath = outputPath;
	}
	public void setImageFilePath(String imageFilePath) {
		this.imageFilePath = imageFilePath;
	}

}

